#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto
${HIVE_HOME} -S -e "
-- 客户意向日统计宽表
create table edu_dws.customer_relationship_daycount_test
(
    --维度
    dt                  string  comment '天',             -- 时间
    area                string  comment '所在区域',        -- 地区
    itcast_school_id    int     comment '校区id',         -- 校区
    itcast_school_name  string     comment '校区名称',
    itcast_subject_id   int     comment '学科id',         -- 学科
    itcast_subject_name string     comment '学科名称',
    tdepart_id          int     comment '直属部门',        -- 咨询中心
    tdepart_name        string  comment '部门名称',
    origin_channel      string  comment '来源渠道',        -- 来源渠道
    origin_type         string  comment '数据来源（online-线上，offline-线下）',        -- 线上线下
    customer_state      string  comment '学员状态（new_customer-新学员，old_customer-老学员）',       -- 新老学员
    -- 分组标记
    group_type          string  comment 'dt,area,school,subject,tdepart,channel,origin_type,customer_state',
    -- 指标
    customer_rela_cnt   bigint     comment '意向用户个数'
)
comment '客户意向日统计宽表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;


-----------------------------------------------------------
-- 客户线索每小时统计表
create table edu_dws.customer_clue_hourcount_test (
-- 维度
hour                string          comment '小时',
customer_state      string          comment '学员状态（new_customer-新学员，old_customer-老学员）',  -- 新老学员
origin_type         string          comment '数据来源（online-线上，offline-线下）',       -- 线上线下
-- 指标
clue_effec_cnt      bigint          comment '有效线索个数',
clue_total_cnt      bigint          comment '总线索个数'
)
comment '客户线索每小时统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;


-- 客户意向总统计宽表
create table edu_dm.customer_relationship_count_test
(
    -- 时间标记
    time_type           string  comment 'day,month,year',
    year                string  comment '年',
    month               string  comment '月',
    --维度
    dt                  string  comment '天',             -- 时间
    area                string  comment '所在区域',        -- 地区
    itcast_school_id    int     comment '校区id',         -- 校区
    itcast_school_name  string  comment '校区名称',
    itcast_subject_id   int     comment '学科id',         -- 学科
    itcast_subject_name string  comment '学科名称',
    tdepart_id          int     comment '直属部门',        -- 咨询中心
    tdepart_name        string  comment '部门名称',
    origin_channel      string  comment '来源渠道',        -- 来源渠道
    origin_type         string  comment '数据来源（online-线上，offline-线下',  -- 线上线下
    customer_state      string  comment '学员状态（new_customer-新学员，old_customer-老学员）',       -- 新老学员
    -- 分组标记
    group_type          string  comment 'dt,area,school,subject,tdepart,channel,origin_type,customer_state',
    -- 指标
    customer_rela_cnt   bigint     comment '意向用户个数'
)
comment '客户意向总统计宽表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;


-----------------------------------------------------------
-- 客户线索总统计表
create table edu_dm.customer_clue_count_test (
-- 时间标记
time_type           string          comment 'hour,day',
dt                  string          comment '天',
-- 维度
hour                string          comment '小时',
customer_state      string          comment '学员状态（new_customer-新学员，old_customer-老学员',  -- 新老学员
origin_type         string          comment '数据来源（online-线上，offline-线下',                -- 线上线下
-- 指标
clue_effec_cnt      bigint          comment '有效线索个数',
clue_total_cnt      bigint          comment '总线索个数'
)
comment '客户线索总统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;
"


presto --catalog hive --server hadoop01:8090 --execute"
-- 插入数据到意向日统计宽表  hive.edu_dws.customer_relationship_daycount
insert into hive.edu_dws.customer_relationship_daycount_test
with t as (
    select
        dt,
        area,
        itcast_school_id,
        itcast_school_name,
        itcast_subject_id,
        itcast_subject_name,
        tdepart_id,
        tdepart_name,
        origin_channel,
        origin_type,
        customer_state,
        id,
        -- 去重
        row_number() over (partition by id,origin_type,customer_state) as id_rn,
        row_number() over (partition by id,area,origin_type,customer_state) as area_rn,
        row_number() over (partition by id,itcast_subject_id,origin_type,customer_state) as subject_rn,
        row_number() over (partition by id,itcast_school_id,origin_type,customer_state) as school_rn,
        row_number() over (partition by id,origin_channel,origin_type,customer_state) as channel_rn,
        row_number() over (partition by id,tdepart_id,tdepart_name,origin_type,customer_state) as tdepart_rn
    from hive.edu_dwb.customer_relationship_detail_test
)
select dt,
       area,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       tdepart_id,
       tdepart_name,
       origin_channel,
       origin_type,
       customer_state,
       case
           when grouping(area) = 0 then 'area'
           when grouping(itcast_school_id) = 0 then 'school'
           when grouping(itcast_subject_id) = 0 then 'subject'
           when grouping(origin_channel) = 0 then 'channel'
           when grouping(tdepart_id) = 0 then 'tdepart'
           when grouping(dt) = 0 then 'all'
           end as group_type,
       case
           when grouping(area) = 0 then count(if(area_rn = 1 and area is not null, id, null))
           when grouping(itcast_school_id) = 0
               then count(if(school_rn = 1 and itcast_school_id is not null, id, null))
           when grouping(itcast_subject_id) = 0
               then count(if(subject_rn = 1 and itcast_subject_id is not null, id, null))
           when grouping(origin_channel) = 0 then count(if(channel_rn = 1 and origin_channel is not null, id, null))
           when grouping(tdepart_id) = 0 then count(if(tdepart_rn = 1 and tdepart_id is not null, id, null))
           when grouping(dt) = 0 then count(if(id_rn = 1 and dt is not null, id, null))
           end as customer_rela_cnt
from t
group by
    grouping sets ( (dt, origin_type, customer_state),
                    (dt, area, origin_type, customer_state),
                    (dt, itcast_subject_id, itcast_subject_name, origin_type, customer_state),
                    (dt, itcast_school_id, itcast_school_name, origin_type, customer_state),
                    (dt, origin_channel, origin_type, customer_state),
                    (dt, tdepart_id, tdepart_name, origin_type, customer_state)
    )
;

-- 插入数据到线索每小时统计宽表
insert into edu_dws.customer_clue_hourcount_test
select
"hour",
customer_state,
origin_type,
count(if(appeal_status != 1,id,null)) as clue_effec_cnt,
count(id) as clue_total_cnt
from hive.edu_dwb.customer_clue_detail_test
group by hour,origin_type,customer_state
;

-- 插入数据到意向总统计宽表
insert into hive.edu_dm.customer_relationship_count_test
with t as (
    select distinct
    substring (dt,1,4) as year,
    substring (dt,1,7) as month,
    dt
    from hive.edu_dws.customer_relationship_daycount_test
),t2 as (
select
    case when grouping (dt) = 0 then 'dt'
    when grouping (month) = 0 then 'month'
    when grouping (year) = 0 then 'year'
    else 'others' end as time_type,
    year,
    month,
    dt,
    area,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    tdepart_id,
    tdepart_name,
    origin_channel,
    origin_type,
    customer_state,
    group_type as group_type_old,
    case when grouping (area) = 0 then 'area'
    when grouping (itcast_school_id) = 0 then 'school'
    when grouping (itcast_subject_id) = 0 then 'subject'
    when grouping (origin_channel) = 0 then 'channel'
    when grouping (tdepart_id) = 0 then 'tdepart'
    else 'all'
    end as group_type_new,
    sum (customer_rela_cnt) as customer_rela_cnt
from hive.edu_dws.customer_relationship_daycount_test
    left join t using (dt)
group by
    grouping sets (
    (year, origin_type, customer_state, group_type),
    (year, area, origin_type, customer_state, group_type),
    (year, itcast_subject_id, itcast_subject_name, origin_type, customer_state, group_type),
    (year, itcast_school_id, itcast_school_name, origin_type, customer_state, group_type),
    (year, origin_channel, origin_type, customer_state, group_type),
    (year, tdepart_id, tdepart_name, origin_type, customer_state, group_type),
    (year, month, origin_type, customer_state, group_type),
    (year, month, area, origin_type, customer_state, group_type),
    (year, month, itcast_subject_id, itcast_subject_name, origin_type, customer_state, group_type),
    (year, month, itcast_school_id, itcast_school_name, origin_type, customer_state, group_type),
    (year, month, origin_channel, origin_type, customer_state, group_type),
    (year, month, tdepart_id, tdepart_name, origin_type, customer_state, group_type),
    (dt, origin_type, customer_state, group_type),
    (dt, area, origin_type, customer_state, group_type),
    (dt, itcast_subject_id, itcast_subject_name, origin_type, customer_state, group_type),
    (dt, itcast_school_id, itcast_school_name, origin_type, customer_state, group_type),
    (dt, origin_channel, origin_type, customer_state, group_type),
    (dt, tdepart_id, tdepart_name, origin_type, customer_state, group_type)
    )
)
select
    time_type,
    "year",
    "month",
    dt,
    area,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    tdepart_id,
    tdepart_name,
    origin_channel,
    origin_type,
    customer_state,
    group_type_new as group_type,
    customer_rela_cnt
from t2
where group_type_old = group_type_new
;

-- 插入数据到线索总统计宽表
insert into hive.edu_dm.customer_clue_count_test
with t as (
    select distinct
        hour,
        substring(hour,1,10) as dt
    from hive.edu_dws.customer_clue_hourcount_test
)
select
    case
    when grouping (hour) = 0 then 'hour'
    when grouping (dt) = 0 then 'dt'
    else 'other' end as time_type,
    dt,
    hour,
    customer_state,
    origin_type,
    sum (clue_effec_cnt) as clue_effec_cnt,
    sum (clue_total_cnt) as clue_total_cnt
from hive.edu_dws.customer_clue_hourcount_test
left join t using (hour)
group by
grouping sets (
    (hour, origin_type, customer_state),
    (dt, origin_type, customer_state)
)
;
"